import pymysql
import re
import sqlutils.dbconfig as db
from utils.logger import loger

GOLD_BODY_PARAMS = "id int(12) auto_increment primary key,text text not null,time int(12) not null,contact int(12) not null,extract varchar(255) null"
GOLD_URL_PARAMS = "id int(10) auto_increment primary key,url varchar(255) not null,time int(12) not null"

con = None
cur = None


def dbconnect(dbName=None):
    """
    建立数据库连接
    """
    try:
        global con, cur
        con = pymysql.connect(**db.config)
        con.autocommit(1)
        cur = con.cursor()
        if dbName is not None:
            con.select_db(dbName)
        return cur
    except Exception as err:
        loger.info(str(err))
        return None


# 关闭数据库连接
def close():
    if not con:
        con.close()
    else:
        loger.info("DataBase doesn't connect,close connectiong error;please check the db config.")


# 创建数据库
def createDataBase(DB_NAME):
    # 创建数据库
    global cur
    cur.execute(
        'CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci' % DB_NAME)
    con.select_db(DB_NAME)
    loger.info('creatDatabase:' + DB_NAME)


def selectDataBase(DB_NAME):
    """
    选择数据库
    """
    try:
        global con
        con.select_db(DB_NAME)
        return True
    except Exception as err:
        loger.info(str(err))
        return False


# 获取最新时间
def htLastTime(table_name, time_name):
    last = select(table_name, fields=[time_name],
                  complex='' + time_name + '=(select max(' + time_name + ') from ' + table_name + ')')
    if last is not None and len(last) != 0:
        return last[0][time_name]
    else:
        return 0


# 重新创建表
def reCreatable(tablename, direct=False):
    if direct is False:
        if len(select(tablename, fields=["id"])) == 0:
            executeCommit("truncate table " + tablename)
    else:
        executeCommit("truncate table " + tablename)


# 获取数据库版本号
def getVersion():
    cur.execute("SELECT VERSION()")
    return getOneData()


# 获取上个查询的结果
def getOneData():
    # 取得上个查询的结果，是单个结果
    data = cur.fetchone()
    return data


# 创建数据表
def creatTable(tablename, attrdict='', constraint=''):
    """创建数据库表
        args：
            tablename  ：表名字
            attrdict   ：属性键值对,{'book_name':'varchar(200) NOT NULL'...}
            constraint ：主外键约束,PRIMARY KEY(`id`)
    """
    if isExistTable(tablename):
        loger.info("%s is exit" % tablename)
        return
    sql = ''
    sql_mid = '`id` bigint(11) NOT NULL AUTO_INCREMENT,'
    if attrdict != '':
        for attr, value in attrdict.items():
            sql_mid = sql_mid + '`' + attr + '`' + ' ' + value + ','

    sql = sql + 'CREATE TABLE IF NOT EXISTS %s (' % tablename
    sql = sql + sql_mid
    sql = sql + constraint
    sql = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8'
    loger.info('creatTable:' + sql)
    executeCommit(sql)


def executeSql(sql=''):
    """执行sql语句，针对读操作返回结果集
        args：
            sql  ：sql语句
    """
    global cur
    cur.execute(sql)
    records = cur.fetchall()
    return records


def executeCommit(sql=''):
    """执行数据库sql语句，针对更新,删除,事务等操作失败时回滚
    """
    try:
        cur.execute(sql)
        con.commit()
        return True
    except pymysql.Error as e:
        con.rollback()
        error = 'MySQL execute failed! ERROR (%s): %s' % (e.args[0], e.args[1])
        loger.info("error:" + error)
        return False


def insert(tablename, params):
    """创建数据库表
        args：
            tablename  ：表名字
            key        ：属性键
            value      ：属性值
    """
    key = []
    value = []
    for tmpkey, tmpvalue in params.items():
        key.append(tmpkey)
        if isinstance(tmpvalue, str):
            value.append("\'" + tmpvalue + "\'")
        else:
            value.append(tmpvalue)
    attrs_sql = '(' + ','.join(key) + ')'
    values_sql = ' values(' + ','.join(value) + ')'
    sql = 'insert into %s' % tablename
    sql = sql + attrs_sql + values_sql
    return executeCommit(sql)


def select(sql):
    """
    查询数据
    """
    return executeSql(sql)


def insertMany(table, attrs, values):
    """插入多条数据
        args：
            tablename  ：表名字
            attrs        ：属性键
            values      ：属性值

        example：
            table='test_mysqldb'
            key = ["id" ,"name", "age"]
            value = [[101, "liuqiao", "25"], [102,"liuqiao1", "26"], [103 ,"liuqiao2", "27"], [104 ,"liuqiao3", "28"]]
            mydb.insertMany(table, key, value)
    """
    values_sql = ['%s' for v in attrs]
    attrs_sql = '(' + ','.join(attrs) + ')'
    values_sql = ' values(' + ','.join(values_sql) + ')'
    sql = 'insert into %s' % table
    sql = sql + attrs_sql + values_sql
    loger.info('insertMany:' + sql)
    try:
        loger.info(sql)
        for i in range(0, len(values), 20000):
            cur.executemany(sql, values[i:i + 20000])
            con.commit()
    except pymysql.Error as e:
        con.rollback()
        error = 'insertMany executemany failed! ERROR (%s): %s' % (e.args[0], e.args[1])
        loger.info(error)


def backup(source_name, fileds, tager_name, where_tager_name):
    '''
    数据表备份
    :param source_name:
    :param filed:       []
    :param tager_name:
    :param where_tager_name:
    :return:
    '''
    values_sql = ""
    if len(fileds) != 0:
        for index in range(len(fileds)):
            values_sql = values_sql + "" + fileds[index]
            if index != len(fileds) - 1:
                values_sql = values_sql + ","
    executeCommit(
        "INSERT INTO " + tager_name + " (" + values_sql + ") SELECT " + values_sql + " FROM " + source_name + " where " + where_tager_name)


def delete(tablename, cond_dict='', complex=''):
    """删除数据
        args：
            tablename  ：表名字
            cond_dict  ：删除条件字典

        example：
            params = {"name" : "caixinglong", "age" : "38"}
            mydb.delete(table, params)
    """
    consql = ' '
    if cond_dict != '':
        for k, v in cond_dict.items():
            if isinstance(v, str):
                v = "\'" + v + "\'"
            consql = consql + tablename + "." + k + '=' + v + ' and '

    if complex != '':
        consql = consql + ' ' + complex + ' and'

    consql = consql + ' 1=1 '
    sql = "DELETE FROM %s where%s" % (tablename, consql)
    return executeCommit(sql)


def derepetition(tablename, filed1, filed2):
    '''           去除重复字段
    :return:  p1.contact=p2.contact and p1.id > p2.id;
    '''
    sql = "DELETE p1 FROM " + tablename + " as p1," + tablename \
          + " as p2 where " + "p1." + filed1 + "=p2." + filed1 \
          + " and " + "p1." + filed2 + "> p2." + filed2
    return executeCommit(sql)


def rebuildIndex(tableName):
    '''
    删除数据，并且重建索引
    '''
    sql = "truncate " + tableName
    return executeCommit(sql)


def update(tablename, attrs_dict, cond_dict):
    """更新数据
        args：
            tablename  ：表名字
            attrs_dict  ：更新属性键值对字典
            cond_dict  ：更新条件字典

        example：
            params = {"name" : "caixinglong", "age" : "38"}
            cond_dict = {"name" : "liuqiao", "age" : "18"}
            mydb.update(table, params, cond_dict)

    """
    attrs_list = []
    consql = ' '
    for tmpkey, tmpvalue in attrs_dict.items():
        attrs_list.append("`" + tmpkey + "`" + "=" + "\'" + tmpvalue + "\'")
    attrs_sql = ",".join(attrs_list)
    if cond_dict != '':
        for k, v in cond_dict.items():
            if isinstance(v, str):
                v = "\'" + v + "\'"
            consql = consql + "`" + tablename + "`." + "`" + k + "`" + '=' + v + ' and '
    consql = consql + ' 1=1 '
    sql = "UPDATE %s SET %s where%s" % (tablename, attrs_sql, consql)
    return executeCommit(sql)


def dropTable(tablename):
    """删除数据库表
        args：
            tablename  ：表名字
    """
    sql = "DROP TABLE  %s" % tablename
    executeCommit(sql)


def deleteTable(tablename):
    """清空数据库表
        args：
            tablename  ：表名字
    """
    sql = "DELETE FROM %s" % tablename
    loger.info("sql=", sql)
    executeCommit(sql)


def isExistTable(tablename):
    """判断数据表是否存在
        args：
            tablename  ：表名字
        Return:
            存在返回True，不存在返回False
    """
    sql = "select * from %s" % tablename
    result = executeCommit(sql)
    if result is None:
        return True
    else:
        if re.search("doesn't exist", result):
            return False
        else:
            return True

# if __name__ == "__main__":
# # 创建数据库
# result = huitongDB.insert("gold_url", {"name": "testuser"})
# print(result)

#
# # 创建表
# TABLE_NAME = 'test_user'
# loger.info("========= 选择数据表%s ===========" % TABLE_NAME)
# # CREATE TABLE %s(id int(11) primary key,name varchar(30))' %TABLE_NAME
# attrdict = {'name': 'varchar(30) NOT NULL'}
# constraint = "PRIMARY KEY(`id`)"
# # mydb.creatTable(TABLE_NAME,attrdict,constraint)
#
# # 插入纪录
# loger.info("========= 单条数据插入 ===========")
# params = {}
# for i in range(5):
#     params.update({"name": "testuser" + str(i)})  # 生成字典数据，循环插入
#     loger.info(params)
#     mydb.insert(TABLE_NAME, params)
#     loger.info("")
#
# # 批量插入数据
# loger.info("========= 多条数据同时插入 ===========")
# insert_values = []
# for i in range(5):
#     # values.append((i,"testuser"+str(i)))
#     insert_values.append([u"测试用户" + str(i)])  # 插入中文数据
# loger.info(insert_values)
# insert_attrs = ["name"]
# mydb.insertMany(TABLE_NAME, insert_attrs, insert_values)
#
# # 数据查询
# loger.info("========= 数据查询 ===========")
# loger.info(mydb.select(TABLE_NAME, fields=["id", "name"]))
# loger.info(mydb.select(TABLE_NAME, cond_dict={'name': '测试用户2'}, fields=["id", "name"]))
# loger.info(mydb.select(TABLE_NAME, cond_dict={'name': '测试用户2'}, fields=["id", "name"], order="order by id desc"))
#
# # 删除数据
# loger.info("========= 删除数据 ===========")
# delete_params = {"name": "测试用户2"}
# mydb.delete(TABLE_NAME, delete_params)
#
# # 更新数据
# loger.info("========= 更新数据 ===========")
# update_params = {"name": "测试用户99"}  # 需要更新为什么值
# update_cond_dict = {"name": "测试用户3"}  # 更新执行的查询条件
# mydb.update(TABLE_NAME, update_params, update_cond_dict)
#
# # 删除表数据
# loger.info("========= 删除表数据 ===========")
# mydb.deleteTable(TABLE_NAME)
#
# # 删除表
# loger.info("========= 删除表     ===========")
# mydb.dropTable(TABLE_NAME)
